/*
 * Local Projects Database
 * (c) 2003 by eRomania Gateway
 * Created on Jan 15, 2004
 *
 */
package ro.gateway.aida.usr.messaging;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.DBWrapable;
import ro.gateway.aida.db.DBWrapper;
import ro.gateway.aida.db.PersistenceToken;



/**
 * AlerterDB
 * @author Mihai Postelnicu <p>
 *  mihai@ro-gateway.org
 */
public class AlerterDB extends DBPersistenceManager implements DBWrapable
{
      private AlerterDB( PersistenceToken token )
      {
            super( token );
      }

      public static AlerterDB getManager( PersistenceToken token )
      {
            return new AlerterDB( token );
      }

      public void synchronize( Object o ) throws Exception
      {
            Alerter m = ( Alerter ) o;

            if ( m.getId() == -1 )
                  add( m );
            else
                  update( m );
      }

      public void delete( Object o ) throws Exception
      {
            Alerter m = ( Alerter ) o;
            Connection con = getConnection();
            DBWrapper db = DBWrapper.getManager( token );

            //wipe related filters
            db.delete( new ProjectFilter( -1, m.getId() ) );

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE );
                  ps.setLong( 1, m.getId() );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      protected void update( Object o ) throws Exception
      {
            Alerter m = ( Alerter ) o;
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( UPDATE );
                  ps.setString( 1, bool2Str( m.isMailable() ) );
                  ps.setString( 2, m.getName() );
                  ps.setString( 3, m.getPurpose() );
                  ps.setInt( 4, m.getAlerterType() );
                  ps.setInt( 5, m.getPriority() );
                  ps.setString( 6, bool2Str( m.isEnabled() ) );
                  ps.setString( 7, bool2Str( m.isIntersection() ) );
                  ps.setLong( 8, m.getId() );

                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      protected Object getById( Object o ) throws Exception
      {
            Alerter m = ( Alerter ) o;
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET );
                  ps.setLong( 1, m.getId() );

                  ResultSet rs = ps.executeQuery();

                  if ( rs.next() )
                  {
                        m.setMailable( str2Bool( rs.getString( 1 ) ) );
                        m.setName( rs.getString( 2 ) );
                        m.setPurpose( rs.getString( 3 ) );
                        m.setAlerterType( rs.getInt( 4 ) );
                        m.setPriority( rs.getInt( 5 ) );
                        m.setEnabled( str2Bool( rs.getString( 6 ) ) );
                        m.setUserId( rs.getLong( 7 ) );
                        m.setIntersection( str2Bool( rs.getString( 8 ) ) );
                  }
                  else
                        m.setId( -1 );

                  rs.close();
                  ps.close();
            }
            finally
            {
                  con.close();
            }

            return m;
      }

      protected void add( Object o ) throws Exception
      {
            Alerter m = ( Alerter ) o;
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( ADD );
                  ps.setString( 1, bool2Str( m.isMailable() ) );
                  ps.setString( 2, m.getName() );
                  ps.setString( 3, m.getPurpose() );
                  ps.setInt( 4, m.getAlerterType() );
                  ps.setInt( 5, m.getPriority() );
                  ps.setString( 6, bool2Str( m.isEnabled() ) );
                  ps.setLong( 7, m.getUserId() );
                  ps.setString( 8, bool2Str( m.isIntersection() ) );
                  ps.executeUpdate();
                  ps.close();
            }
            finally
            {
                  con.close();
            }
      }

      public Object get( Object o ) throws Exception
      {
            Alerter m = ( Alerter ) o;

            if ( m.getId() != -1 ) return getById( m );
            if ( m.getUserId() != -1 )
                  return getForUser( m );

            //else get them all, if needed
            return getAll();
      }

      protected List getAll() throws Exception
      {
            Connection con = getConnection();
            ArrayList ret = new ArrayList();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET_ALL );

                  ResultSet rs = ps.executeQuery();

                  while ( rs.next() )
                  {
                        Alerter t = new Alerter( rs.getLong( 1 ), rs.getLong( 8 ) );
                        t.setMailable( str2Bool( rs.getString( 2 ) ) );
                        t.setName( rs.getString( 3 ) );
                        t.setPurpose( rs.getString( 4 ) );
                        t.setAlerterType( rs.getInt( 5 ) );
                        t.setPriority( rs.getInt( 6 ) );
                        t.setEnabled( str2Bool( rs.getString( 7 ) ) );
                        t.setIntersection( str2Bool( rs.getString( 9 ) ) );
                        ret.add( t );
                  }

                  rs.close();
                  ps.close();
            }
            finally
            {
                  con.close();
            }

            return ret;
      }

      protected List getForUser( Alerter m ) throws Exception
      {
            Connection con = getConnection();
            ArrayList ret = new ArrayList();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET_FOR_USER );
                  ps.setLong( 1, m.getUserId() );

                  ResultSet rs = ps.executeQuery();

                  while ( rs.next() )
                  {
                        Alerter t = new Alerter( rs.getLong( 1 ), m.getUserId() );
                        t.setMailable( str2Bool( rs.getString( 2 ) ) );
                        t.setName( rs.getString( 3 ) );
                        t.setPurpose( rs.getString( 4 ) );
                        t.setAlerterType( rs.getInt( 5 ) );
                        t.setPriority( rs.getInt( 6 ) );
                        t.setEnabled( str2Bool( rs.getString( 7 ) ) );
                        t.setIntersection( str2Bool( rs.getString( 8 ) ) );
                        ret.add( t );
                  }

                  rs.close();
                  ps.close();
            }
            finally
            {
                  con.close();
            }

            return ret;
      }

      public static final String ADD =
            "INSERT INTO alerters (mailable,name,purpose,type,priority,enabled,user_id,intersection) " +
            "VALUES (?,?,?,?,?,?,?,?)";
      public static final String UPDATE =
            "UPDATE alerters SET mailable=?,name=?,purpose=?,type=?,priority=?," +
            "enabled=?, intersection=? WHERE id=?";
      public static final String GET =
            "SELECT mailable,name,purpose,type,priority,enabled,user_id,intersection FROM alerters " +
            "WHERE id=?";
      public static final String GET_FOR_USER =
            "SELECT id,mailable,name,purpose,type,priority,enabled,intersection FROM alerters " +
            "WHERE user_id=?";
      public static final String GET_ALL =
            "SELECT id,mailable,name,purpose,type,priority,enabled,user_id,intersection FROM alerters ";
      public static final String DELETE = "DELETE FROM alerters WHERE id=?";
}
